﻿using DevelopAssistant.Service;
using ICSharpCode.TextEditor.Document;
using ICSharpCode.WinFormsUI.Controls;
using ICSharpCode.WinFormsUI.Docking;
using NORM.Common;
using NORM.DataBase;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace DevelopAssistant.Core.DBMS
{
    public partial class TableDesignnerDocument : DockContent
    {
        int max_rowIndex = 0;
        bool editorable = true;
        string table = string.Empty;
        DataBaseServer server = null;

        private bool indexTabRequired = true;
        private bool triggerTabRequired = true;

        private NComboBox DropDownDataTypes = new NComboBox();

        /// <summary>
        /// 触发器类
        /// </summary>
        public class TriggerInfo
        {
            public int rowIndex { get; set; }
            public string table_name { get; set; }
            public string trigger_type { get; set; }
            public string trigger_name { get; set; }
            public string trigger_script { get; set; }
        }

        /// <summary>
        /// 索引类
        /// </summary>
        public class IndexesInfo
        {
            public int rowIndex { get; set; }
            public string table_name { get; set; }
            public string indexes_type { get; set; }
            public string indexes_name { get; set; }
            public string indexes_script { get; set; }
        }

        protected MainForm OwnerForm { get; set; }

        public TableDesignnerDocument()
        {
            InitializeComponent();
            InitializeControls();
            this.dgv_design.CellClick += new DataGridViewCellEventHandler(this.dataGridView1_CellClick);
            this.dgv_design.RowPostPaint += new DataGridViewRowPostPaintEventHandler(dataGridView1_RowPostPaint);            
        }

        public TableDesignnerDocument(MainForm mainForm,string tableName, DataBaseServer dataBaseServer)
            : this()
        {
            table = tableName;
            server = dataBaseServer;
            OwnerForm = (MainForm)mainForm;
            this.textBox1.Text = table;
            BindDataTypes(server.ProviderName);
        }

        public void InitializeControls()
        {
            this.tableTabPage.ImageIndex = 1;
            this.codeTabPage.ImageIndex = 2;
            this.triggerTabPage.ImageIndex = 3;
            this.indexesTabPage.ImageIndex = 4;

            if (!triggerTabRequired)
                this.tabControl1.TabPages.Remove(this.triggerTabPage);
            if (!indexTabRequired)
                this.tabControl1.TabPages.Remove(this.indexesTabPage);

            this.tabControl1.Radius = 1;          
            this.tabControl1.TabCaptionLm = -3;
            this.tabControl1.ItemSize = new System.Drawing.Size(86, 28);
            this.tabControl1.Alignment = TabAlignment.Bottom;
            this.tabControl1.BaseColor = SystemColors.Control;
            this.tabControl1.BackColor = SystemColors.Control;
            this.tabControl1.SizeMode = System.Windows.Forms.TabSizeMode.Fixed;

            this.textEditorControl1.ShowVRuler = false;
            this.textEditorControl1.BorderVisable = false;
            this.textEditorControl1.FoldMarkStyle = AppSettings.EditorSettings.FoldMarkStyle;
            this.textEditorControl1.LineViewerStyle = LineViewerStyle.None;
            this.textEditorControl1.SetHighlighting(AppSettings.EditorSettings.TSQLEditorTheme, "TSQL");

            this.textEditorControl1.Document.FormattingStrategy = new DefaultFormattingStrategy();
            this.textEditorControl1.Document.FoldingManager.FoldingStrategy = new TSQLFoldingStrategy();
            this.textEditorControl1.TextChanged += textEditorControl_TextChanged;

            this.dgv_design.Controls.Add(DropDownDataTypes);          
            this.DropDownDataTypes.Visible = false;
            this.DropDownDataTypes.BackColor = Color.White;
            this.DropDownDataTypes.DropDownStyle = ComboBoxStyle.DropDownList;
            //this.panel1.TopBlackColor = Color.WhiteSmoke;
            //this.panel1.BottomBlackColor = SystemColors.Control;
            this.DropDownDataTypes.SelectedValueChanged += DropDownDataTypes_SelectedValueChanged;
        }

        private void LoadXTheme()
        {
            this.toolStrip1.Visible = editorable;
            this.tabControl1.BorderColor = SystemColors.ControlDark;
            //this.tabControl1.ArrowColor = Color.White;
            this.tabControl1.BaseColor = SystemColors.Control;
            this.tabControl1.BackColor = SystemColors.Control;
            this.tabControl1.SelectedColor = SystemColors.Control;
            this.tabControl1.BaseTabColor = SystemColors.Control;
            this.panel1.BorderColor = OwnerForm.XTheme.FormBorderOutterColor;            
        }

        private void textEditorControl_TextChanged(object sender, EventArgs e)
        {
            textEditorControl1.Document.FoldingManager.UpdateFoldings();
        }

        private void TableDesignnerDocument_Load(object sender, EventArgs e)
        {
            LoadXTheme();
            OnThemeChanged(new EventArgs());

            GetTableColumns();

            if(triggerTabRequired)
            {
                GetTriggers();
                GetTriggersScript();
            }
            if (indexTabRequired)
            {
                GetIndexes();
                GetIndexesScript();
            }           
        }

        private void GetTableColumns()
        {
            max_rowIndex = 0; 
            using (var db = Utility.GetAdohelper(server))
            {
                DataTable dt = db.GetTableObject(table);
                DataTable dv = new DataTable();
                dv.Columns.Add(new DataColumn("序号", typeof(Int32)));
                dv.Columns.Add(new DataColumn("名称", typeof(String)));
                dv.Columns.Add(new DataColumn("数据类型", typeof(String)));
                dv.Columns.Add(new DataColumn("长度", typeof(String)));
                dv.Columns.Add(new DataColumn("主键标识", typeof(String)));
                dv.Columns.Add(new DataColumn("允许为空", typeof(String)));
                dv.Columns.Add(new DataColumn("自增标识", typeof(String)));
                dv.Columns.Add(new DataColumn("描述", typeof(String)));
                dv.Columns.Add(new DataColumn("默认值", typeof(String)));
                dv.Columns.Add(new DataColumn("全标识", typeof(String)));

                int index = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    DataRow row = dv.NewRow();
                    string ColumnOrder = dr["colorder"] + "";
                    string ColumnName = dr["ColumnName"] + "";
                    string ColumnTypeName = dr["TypeName"] + "";
                    string ColumnLength = dr["Length"] + "";
                    string ColumnIsNull = dr["CisNull"] + "";
                    string ColumnIdentity = dr["CisNull"] + "";
                    string ColumnPrimaryKey = dr["CisNull"] + "";
                    string columnDescription = dr["Describ"] + "";
                    string columnDefaultValue= dr["DefaultValue"] + "";

                    row["序号"] = index;
                    if (!string.IsNullOrEmpty(ColumnOrder))
                    {
                        row["序号"] = Int32.Parse(ColumnOrder);
                    }
                    row["名称"] = ColumnName;

                    switch (ColumnTypeName.ToLower())
                    {
                        case "int":
                        case "int32":
                        case "int64":
                        case "number":
                        case "image":
                        case "bit":
                        case "integer":
                            ColumnLength = "";
                            break;
                    }

                    row["数据类型"] = ColumnTypeName.ToLower();
                    row["长度"] = ColumnLength;

                    //if (ColumnIsNull.Contains("not null"))
                    //{
                    //    ColumnIdentity = "×";//Identity
                    //}
                    //else
                    //{
                    //    ColumnIdentity = "√";
                    //}

                    //if (ColumnIsNull.Contains("pk"))
                    //{
                    //    ColumnIdentity += " P";
                    //}

                    //if (ColumnIsNull.Contains("identity"))
                    //{
                    //    ColumnIdentity += " ↑";
                    //}

                    if (ColumnIsNull.Contains("pk"))
                    {
                        ColumnPrimaryKey = "Pk";
                    }
                    else
                    {
                        ColumnPrimaryKey = "";
                    }

                    row["主键标识"] = ColumnPrimaryKey;

                    if (ColumnIsNull.Contains("identity"))
                    {
                        ColumnIdentity = "自增";
                    }
                    else
                    {
                        ColumnIdentity = "";
                    }

                    row["自增标识"] = ColumnIdentity;

                    if (ColumnIsNull.Contains("not null"))
                    {
                        ColumnIsNull = "False";
                    }
                    else
                    {
                        ColumnIsNull = "True";
                    }

                    row["允许为空"] = Boolean.Parse(ColumnIsNull);

                    row["描述"] = columnDescription;

                    row["默认值"] = columnDefaultValue;

                    row["全标识"] = dr["CisNull"] + "";

                    dv.Rows.Add(row);
                    index++;
                    max_rowIndex++;
                }

                dgv_design.DataSource = dv.DefaultView;
            }
        }

        private void GetTriggers()
        {
            //获取表触发器
            string triggerSql = "";
            DataTable triggerTable = null;

            try
            {
                dgv_trigger.ReadOnly = true;

                using (var db = DevelopAssistant.Service.Utility.GetAdohelper(server))
                {
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":

                            //triggerSql = "declare @trigger_script varchar(max)=''; ";
                            //triggerSql += "declare @table table(script varchar(max)); ";
                            //triggerSql += "insert into @table exec  sp_helptext '"+this.textBox1.Text+"' ;";
                            //triggerSql += "select @trigger_script=@trigger_script+script+',' from  @table ";
                            //triggerSql += System.Environment.NewLine;

                            triggerSql += "select 0 as colTriggerIndex, a.name as table_name";
                            triggerSql += ",sysobjects.name as trigger_name";
                            triggerSql += ",'未知' as trigger_type";
                            triggerSql += ",'' as trigger_statement";
                            triggerSql += ",'' as trigger_descrip ";
                            triggerSql += "from sysobjects ";
                            triggerSql += "left join (select * from sysobjects where xtype='U')as a ";
                            triggerSql += "on sysobjects.parent_obj=a.id ";
                            triggerSql += "where sysobjects.xtype='TR' and a.name='"+ this.textBox1.Text + "' ";

                            triggerTable = db.QueryDataSet(CommandType.Text,
                               triggerSql, null).Tables[0];

                            dgv_trigger.DataSource = triggerTable;

                            break;

                        case "System.Data.PostgreSql":

                            //triggerSql = "SELECT event_object_table";
                            //triggerSql += ",trigger_name";
                            //triggerSql += ",event_manipulation";
                            //triggerSql += ",action_statement";
                            //triggerSql += ",action_timing ";
                            //triggerSql += "FROM  information_schema.triggers";
                            //triggerSql += "WHERE event_object_table = '" + this.textBox1.Text + "' ";
                            //triggerSql += "ORDER BY event_object_table";
                            //triggerSql += ",event_manipulation ";

                            triggerSql = "SELECT 0 as colTriggerIndex, event_object_table as table_name";
                            triggerSql += ",trigger_name as trigger_name ";
                            triggerSql += ",action_timing as trigger_type ";
                            //triggerSql += ",( SELECT string_agg(event_manipulation||'',' OR ') FROM information_schema.triggers WHERE event_object_table='" + this.textBox1.Text + "' ) as trigger_statement ";
                            triggerSql += ",'' as trigger_statement ";
                            triggerSql += ",'' as trigger_descrip ";
                            triggerSql += "FROM  information_schema.triggers ";
                            triggerSql += "WHERE event_object_table = '" + table + "' ";
                            triggerSql += "GROUP BY event_object_table,trigger_name,action_timing ";
                            triggerSql += "ORDER BY event_object_table";
                            //triggerSql += ",event_manipulation ";

                            triggerTable = db.QueryDataSet(CommandType.Text,
                                triggerSql, null).Tables[0];

                            dgv_trigger.DataSource = triggerTable;

                            break;
                    }
                }

                for (int i = 0; i < dgv_trigger.Rows.Count; i++)
                {
                    DataGridViewRow row = dgv_trigger.Rows[i];
                    row.Cells["colTriggerIndex"].Value = i + 1;
                }
            }
            catch(Exception ex)
            {
                DevelopAssistant.Common.NLogger.WriteToLine(triggerSql, "错误", DateTime.Now, "获取" + this.textBox1.Text + "表的触发器时");
                DevelopAssistant.Common.NLogger.WriteToLine(ex.Message, "错误", DateTime.Now, ex.Source, ex.StackTrace);
            }

        }

        private async void GetTriggersScript()
        {
            this.tabControl1.Enabled = false;                   
            List<TriggerInfo> triggerList = new List<TriggerInfo>();
            foreach (DataGridViewRow row in this.dgv_trigger.Rows)
            {
                TriggerInfo trigger = new TriggerInfo();
                trigger.rowIndex = row.Index;
                trigger.table_name = table;
                trigger.trigger_name = row.Cells["colTriggerName"].Value + "";
                trigger.trigger_type= row.Cells["colTriggerType"].Value + "";
                triggerList.Add(trigger);                 
            }
            await LoadTriggersScript(triggerList);
        }

        private async Task<bool> LoadTriggersScript(object args)
        {
            List<TriggerInfo> triggers = (List<TriggerInfo>)args;

            bool result = await System.Threading.Tasks.Task.Run<bool>(() =>
            {
                string triggerSql = "";
                string triggerScript = "";

                try
                {
                    NORM.DataBase.DataBaseTypes databaseType
                    = NORM.DataBase.DataBaseTypes.SqlDataBase;

                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":

                            triggerSql = "declare @trigger_script varchar(max)=''; ";
                            triggerSql += "declare @table table(script varchar(max)); ";
                            triggerSql += "insert into @table exec  sp_helptext '#trigger_name#' ;";
                            triggerSql += "select @trigger_script=@trigger_script+script+'' from  @table ";
                            triggerSql += "select @trigger_script as trigger_script;";
                            triggerSql += System.Environment.NewLine;

                            databaseType = NORM.DataBase.DataBaseTypes.SqlDataBase;
                            break;
                        case "System.Data.PostgreSql":

                            //triggerSql = "SELECT string_agg(event_manipulation||'',' OR ') ";
                            triggerSql = "SELECT event_manipulation,action_statement ";
                            triggerSql += "FROM information_schema.triggers WHERE event_object_table='" + table + "' ";

                            databaseType = NORM.DataBase.DataBaseTypes.PostgreSqlDataBase;
                            break;
                    }

                    using (var db = NORM.DataBase.DataBaseFactory.Create(server.ConnectionString, databaseType))
                    {
                        for (int i = 0; i < triggers.Count; i++)
                        {
                            TriggerInfo triggerInfo = triggers[i];
                            string trigger_name = triggerInfo.trigger_name;
                            string trigger_type = triggerInfo.trigger_type;
                            string sql = triggerSql.Replace("#trigger_name#", trigger_name);
                            DataTable dt = db.QueryTable(CommandType.Text, sql, null);

                            switch (server.ProviderName)
                            {
                                case "System.Data.Sql":
                                case "System.Data.SQL":
                                    triggerScript = dt.Rows[0]["trigger_script"] + "";
                                    break;
                                case "System.Data.PostgreSql":

                                    triggerScript = "CREATE TRIGGER " + trigger_name + " " + trigger_type + " ";

                                    string event_manipulation = string.Empty;
                                    string action_statement = string.Empty;

                                    for (int j = 0; j < dt.Rows.Count; j++)
                                    {
                                        DataRow row = dt.Rows[j];
                                        if (j == 0)
                                        {
                                            event_manipulation += row["event_manipulation"] + "";
                                        }
                                        else
                                        {
                                            event_manipulation += " OR " + row["event_manipulation"] + "";
                                        }

                                        action_statement = row["action_statement"] + "";
                                    }

                                    triggerScript += System.Environment.NewLine;
                                    triggerScript += event_manipulation;
                                    triggerScript += System.Environment.NewLine;
                                    triggerScript += "ON public." + table;
                                    triggerScript += System.Environment.NewLine;
                                    triggerScript += "FOR EACH ROW ";
                                    triggerScript += action_statement;

                                    break;
                            }

                            triggerInfo.trigger_script = triggerScript;

                        }
                    }
                }
                catch(Exception ex)
                {
                    DevelopAssistant.Common.NLogger.WriteToLine(ex.Message, "错误", DateTime.Now, ex.Source, ex.StackTrace);
                }

                return true;

            });

            if (result)
            {
                foreach(TriggerInfo trigger in triggers)
                {
                    this.dgv_trigger.Rows[trigger.rowIndex].Cells["colTrigger_statement"].Value = trigger.trigger_script + "";
                }
            }

            this.tabControl1.Enabled = true;

            return result;

        }

        private void GetIndexes()
        {
            string indexesSql = "";
            DataTable indexesTable = null;

            try
            {
                StringBuilder sb = new StringBuilder();

                using (var db = DevelopAssistant.Service.Utility.GetAdohelper(server))
                {
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":
                           
                            sb.Append("select row_index,index_name,table_name ");//index_type,
                            sb.Append(",(case index_type when '2066' then '聚集' when '2' then '唯一,非聚集' else '不唯一,非聚集' end) as index_type ");
                            //sb.Append(",( ");
                            //sb.Append("select stuff((select ','+name ");
                            //sb.Append("from (");
                            //sb.Append("SELECT d .name as name ");
                            //sb.Append("FROM sysindexes a JOIN sysindexkeys b ON a .id = b .id  AND a .indid = b.indid ");
                            //sb.Append("JOIN sysobjects c ON b .id = c .id ");
                            //sb.Append("JOIN syscolumns d ON b .id = d .id  AND b .colid = d .colid ");
                            //sb.Append("WHERE c.xtype='U'  AND c .name = 'Accounts' and a.name=t.index_name ");
                            //sb.Append(") t for xml path('')),1,1,'') as name ");
                            //sb.Append(") as index_descrip ");
                            sb.Append(",'' as index_descrip ");
                            sb.Append(",'' as index_statement ");
                            sb.Append("from ( ");
                            sb.Append("SELECT  ROW_NUMBER() over(Order by c.name ,a.name ) as row_index, a.name index_name, c.name table_name, a.status index_type ");
                            sb.Append("FROM  sysindexes a JOIN sysindexkeys b ON a.id = b.id  AND a.indid = b.indid ");
                            sb.Append("JOIN sysobjects c ON b.id = c.id ");
                            sb.Append("JOIN syscolumns d ON b.id = d.id  AND b.colid = d.colid ");
                            sb.Append("WHERE  c.xtype='U' ");
                            sb.Append("AND c.name = '"+table+"' ");
                            sb.Append("group by a.name,a.status,c.name ) t");

                            indexesSql = sb.ToString();

                            indexesTable = db.QueryDataSet(CommandType.Text, indexesSql, null).Tables[0];

                            break;
                        case "System.Data.PostgreSql":

                            sb.Append("select row_number() over() as row_index, indexname as index_name ");
                            sb.Append(",tablename as table_name,'用户' as index_type ");
                            sb.Append(",'' as index_descrip ");
                            sb.Append(",'' as index_statement ");
                            sb.Append("from pg_indexes ");
                            sb.Append("where tablename='" + table + "' ");

                            indexesSql = sb.ToString();

                            indexesTable = db.QueryDataSet(CommandType.Text, indexesSql, null).Tables[0];

                            break;
                    }
                }

                this.dgv_indexes.DataSource = indexesTable;

            }
            catch(Exception ex)
            {
                DevelopAssistant.Common.NLogger.WriteToLine(ex.Message, "错误", DateTime.Now, ex.Source, ex.StackTrace);
                DevelopAssistant.Common.NLogger.WriteToLine(indexesSql, "错误", DateTime.Now, ex.Source, ex.StackTrace);
            }
        }

        private async void GetIndexesScript()
        {
            this.tabControl1.Enabled = false;
            List<IndexesInfo> indexList = new List<IndexesInfo>();
            foreach (DataGridViewRow row in this.dgv_indexes.Rows)
            {
                IndexesInfo indexes = new IndexesInfo();
                indexes.rowIndex = row.Index;
                indexes.table_name = table;
                indexes.indexes_type = row.Cells["colIndexType"].Value + "";
                indexes.indexes_name = row.Cells["colIndexName"].Value + "";
                indexList.Add(indexes);
            }
            await LoadIndexesScript(indexList);
        }

        private async Task<bool> LoadIndexesScript(object args)
        {
            List<IndexesInfo> indexList = (List<IndexesInfo>)args;
            var result= await System.Threading.Tasks.Task.Run<bool>(()=> {

                string indexesSql = "";
                string indexesScript = "";

                try
                {
                    NORM.DataBase.DataBaseTypes databaseType
                   = NORM.DataBase.DataBaseTypes.SqlDataBase;

                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":

                            indexesSql = "SELECT d.name as name ";
                            indexesSql += "FROM sysindexes a JOIN sysindexkeys b ON a.id = b.id  AND a.indid = b.indid ";
                            indexesSql += "JOIN sysobjects c ON b.id = c.id ";
                            indexesSql += "JOIN syscolumns d ON b.id = d.id  AND b .colid = d.colid ";
                            indexesSql += "WHERE c.xtype='U'  AND c.name = '"+table+"' ";
                            indexesSql += "AND a.name='#index_name#' ";                            
                            databaseType = NORM.DataBase.DataBaseTypes.SqlDataBase;

                            break;

                        case "System.Data.PostgreSql":

                            indexesSql = "SELECT indexdef as indexscript ";
                            indexesSql += "from pg_indexes ";
                            indexesSql += "where tablename='" + table + "' and indexname='#index_name#' ";
                            databaseType = NORM.DataBase.DataBaseTypes.PostgreSqlDataBase;

                            break;
                    }

                    using (var db = NORM.DataBase.DataBaseFactory.Create(server.ConnectionString, databaseType))
                    {
                        for (int i = 0; i < indexList.Count; i++)
                        {
                            IndexesInfo indexes = indexList[i];
                            string index_name = indexes.indexes_name;
                            string index_type = indexes.indexes_type;

                            string sql = indexesSql.Replace("#index_name#", index_name);

                            DataTable dt = db.QueryTable(CommandType.Text, sql, null);

                            switch (server.ProviderName)
                            {
                                case "System.Data.Sql":
                                case "System.Data.SQL":

                                    if (index_type == "聚集")
                                    {
                                        indexesScript = "ALTER TABLE [dbo].[" + table + "] ADD  CONSTRAINT ["+ index_name + "] PRIMARY KEY CLUSTERED ";
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += "(";
                                    }
                                    else if (index_type == "唯一,非聚集")
                                    {
                                        indexesScript = "CREATE UNIQUE NONCLUSTERED INDEX [" + index_name + "] ON [dbo].[" + table + "] ";
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += "(";
                                    }
                                    else if (index_type == "不唯一,非聚集")
                                    {
                                        indexesScript = "CREATE NONCLUSTERED INDEX [" + index_name + "] ON [dbo].[" + table + "] ";
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += "(";
                                    }

                                    for (int j = 0; j < dt.Rows.Count; j++)
                                    {
                                        DataRow row = dt.Rows[j];
                                        indexesScript += System.Environment.NewLine;
                                        if (j > 0)
                                        {
                                            indexesScript += " ,"+ SnippetBase.GetColumnName(row["name"], server.ProviderName) + " ASC";
                                        }
                                        else
                                        {
                                            indexesScript += "  "+ SnippetBase.GetColumnName(row["name"], server.ProviderName) + " ASC";
                                        }
                                    }

                                    if (index_type == "聚集")
                                    {
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";                                        
                                    }
                                    else if (index_type == "唯一,非聚集")
                                    {
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";                                        
                                    }
                                    else if (index_type == "不唯一,非聚集")
                                    {
                                        indexesScript += System.Environment.NewLine;
                                        indexesScript += ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";                                        
                                    }


                                    break;

                                case "System.Data.PostgreSql":

                                    if (dt != null && dt.Rows.Count > 0)
                                        indexesScript = dt.Rows[0]["indexscript"] + "";

                                    break;

                            }

                            indexes.indexes_script = indexesScript;

                        }
                    }

                }
                catch(Exception ex)
                {
                    DevelopAssistant.Common.NLogger.WriteToLine(ex.Message, "错误", DateTime.Now, ex.Source, ex.StackTrace);
                    DevelopAssistant.Common.NLogger.WriteToLine(indexesScript, "错误", DateTime.Now, ex.Source, ex.StackTrace);
                }               

                return true;
            });

            if (result)
            {
                foreach (IndexesInfo indexes in indexList)
                {
                    this.dgv_indexes.Rows[indexes.rowIndex].Cells["colIndexDescriptoin"].Value = indexes.indexes_script + "";
                    this.dgv_indexes.Rows[indexes.rowIndex].Cells["colIndexStatement"].Value = indexes.indexes_script + "";
                }
            }

            this.tabControl1.Enabled = true;


            return result;
        }

        private void toolStripButtonReturn_Click(object sender, EventArgs e)
        {
            GetTableColumns();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1 && e.ColumnIndex > -1)
            {
                if (!editorable)
                    return;

                if (e.ColumnIndex == 2)
                {
                    DataGridViewCell cell = dgv_design.Rows[e.RowIndex].Cells[e.ColumnIndex];//dataGridView1.CurrentCell; //
                    Rectangle rect = dgv_design.GetCellDisplayRectangle(cell.ColumnIndex, cell.RowIndex, false);
                    DropDownDataTypes.Left = rect.X;
                    DropDownDataTypes.Top = rect.Y;
                    DropDownDataTypes.Width = rect.Width;
                    DropDownDataTypes.Height = rect.Height;
                    DropDownDataTypes.Visible = true;

                    DropDownDataTypes.SelectedItem = cell.Value;

                    if (cell.RowIndex == max_rowIndex && (cell.Value + "") == "")
                    {
                        cell.Value = (string)DropDownDataTypes.SelectedItem;
                    }

                }
                else if (e.ColumnIndex == 4)
                {
                    DropDownDataTypes.Visible = false;                    
                }
                else
                {
                    DropDownDataTypes.Visible = false;
                }
            }
        }

        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                var value = dgv_design.Rows[e.RowIndex].Cells[4].Value;
                if (value != null && value != DBNull.Value)
                {
                    if (value.ToString().Contains("Pk"))
                    {
                        Graphics g = e.Graphics;
                        e.Graphics.DrawImage(this.imageList1.Images[0], e.RowBounds.Left + 18, e.RowBounds.Top + (e.RowBounds.Height - 16) / 2, 16f, 16f);//绘制图标
                    }
                }
            }
        }

        private void btnApplyOk_Click(object sender, EventArgs e)
        {
            SaveChange();
            //this.DialogResult = DialogResult.OK;
            //this.Close();
        }

        private void btnCancle_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void BindDataTypes(string ProviderName)
        {
            string DataBaseTypes = "Sql";
            switch (ProviderName)
            {
                case "System.Data.SQL":
                case "System.Data.Sql":
                    DataBaseTypes = "Sql";
                    break;
                case "System.Data.Sqlite":
                    DataBaseTypes = "Sqlite";
                    break;
                case "System.Data.PostgreSql":
                    DataBaseTypes = "PostgreSql";
                    break;
            }

            string path = Application.StartupPath + "\\" + "DataBaseDataTypes.xml";
            if (System.IO.File.Exists(path))
            {
                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(path);
                XmlNodeList list = xmldoc.SelectSingleNode(string.Format("{0}{1}{2}", 
                    "//", DataBaseTypes, "DataBase")).ChildNodes;

                foreach (XmlNode li in list)
                {
                    ToolStripMenuItem item = new ToolStripMenuItem();
                    item.Text = li.Attributes["DataType"].Value + "";
                    this.DropDownDataTypes.Items.Add(item.Text);
                }

            }
            else
            {
                MessageBox.Show("DataBaseDataTypes.xml文件不存在");
            }

          
        }

        private void DropDownDataTypes_SelectedValueChanged(object sender, EventArgs e)
        {
            try
            {
                ComboBox cbox = (ComboBox)sender;
                DataGridViewCell DCell = dgv_design.CurrentCell;
                if (DCell != null)
                {
                    dgv_design.CurrentCell.Value = cbox.Text;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void toolStripButtonSave_Click(object sender, EventArgs e)
        {
            SaveChange();
        }

        private void SaveChange()
        {
            StringPlus sp = new StringPlus();
            sp.Append("SET ANSI_NULLS ON");
            sp.Append(Environment.NewLine);
            sp.Append("GO");
            sp.Append(Environment.NewLine);
            sp.Append("SET QUOTED_IDENTIFIER ON");
            sp.Append(Environment.NewLine);
            sp.Append("GO");
            sp.Append(Environment.NewLine);
            sp.Append(Environment.NewLine);
            sp.Append("SET ANSI_PADDING ON");
            sp.Append(Environment.NewLine);
            sp.Append("GO");            
            sp.Append(Environment.NewLine);
            sp.Append(Environment.NewLine);
            
            switch (server.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    sp.Append("CREATE TABLE [dbo].[" + this.textBox1.Text + "](");
                    break;
                case "System.Data.Sqlite":
                    sp.Append("CREATE TABLE [" + this.textBox1.Text + "](");
                    break;
                case "System.Data.PostgreSql":
                    sp.Append("CREATE TABLE \"" + this.textBox1.Text + "\"(");
                    break;
            }

            sp.Append(Environment.NewLine);

            int index = 0;
            var dt = (this.dgv_design.DataSource as DataView).Table;

            List<Column> PrimaryKeyList = new List<Column>();
            List<Column> IdentityList = new List<Column>();

            foreach (DataRow row in dt.Rows)
            {
                string identity = row["自增标识"] + "";
                string isnull = row["允许为空"] + "";
                string parmaryKey = row["主键标识"] + "";                
                string sqldataType= SnippetBase.getDataBaseDataType(row["数据类型"] + "", row["长度"] + "", server.ProviderName);

                Column col = new Column();
                col.Name = row["名称"] + "";

                if (!string.IsNullOrEmpty(isnull) && Boolean.Parse(isnull))
                {
                    isnull = "NULL";
                    col.IsNull = "NULL";
                }
                else
                {
                    isnull = "NOT NULL";
                    col.IsNull = "NOT NULL";
                }

                if (identity == "自增")
                {
                    identity = "IDENTITY(1,1)";
                    col.Auto_Identify = true;
                    IdentityList.Add(col);
                }              
                           
                col.Len = row["长度"] + "";
                col.DataType = row["数据类型"] + "";               

                if(index>0)
                {
                    sp.Append("  ," + SnippetBase.GetColumnName(row["名称"] + "", server.ProviderName) + " " + sqldataType + " " + identity + " " + isnull + "");
                }
                else
                {
                    sp.Append("   " + SnippetBase.GetColumnName(row["名称"] + "", server.ProviderName) + " " + sqldataType + " " + identity + " " + isnull + "");
                }

                if (parmaryKey == "Pk")
                {
                    col.Pk_Identify = true;  
                    PrimaryKeyList.Add(col);
                }

                sp.Append(Environment.NewLine);
                index++;
            }

            sp.Append(")");           
            sp.Append(Environment.NewLine);
            sp.Append("GO");
            sp.Append(Environment.NewLine);
            sp.Append(Environment.NewLine);
            sp.Append("SET ANSI_PADDING OFF");
            sp.Append(Environment.NewLine);
            sp.Append("GO");

            using (var db = DevelopAssistant.Service.Utility.GetAdohelper(server))
            {
                string createTable = sp.Value;

                db.BeginTransaction();

                try
                {
                    bool havethistable = false;                     
                    var result = db.GetDataBaseObject().Select("table_name='" + this.textBox1.Text + "' and table_type='table' ");
                    if (result != null && result.Count() > 0)
                        havethistable = true;

                    DataTable column_dt = db.GetTableObject(this.textBox1.Text, DataBaseSchema.Table);

                    if (havethistable)
                    {
                        string renameTable = string.Empty;
                        switch (server.ProviderName)
                        {
                            case "System.Data.Sql":
                            case "System.Data.SQL":
                                renameTable = "EXEC sp_rename '" + this.textBox1.Text + "','" + "_tem_" + this.textBox1.Text + "'";
                                break;
                            case "System.Data.Sqlite":
                                renameTable = "ALTER TABLE " + server.DataBaseName + "." + this.textBox1.Text + " RENAME TO " + "_tem_" + this.textBox1.Text + ";";
                                break;
                            case "System.Data.PostgreSql":
                                renameTable = "ALTER TABLE " + this.textBox1.Text + " RENAME TO " + "_tem_" + this.textBox1.Text + ";";
                                break;
                        }

                        db.Execute(CommandType.Text, renameTable, null);
                    }

                    createTable = createTable.Replace("GO", " ");
                    db.Execute(CommandType.Text, createTable, null);

                    if (havethistable)
                    {
                        int rowscount = 0;
                        string copyDataTable = "select count(*) from " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text, server.ProviderName) + "";
                                               
                        DataTable rowcount_dt = db.QueryDataSet(CommandType.Text, copyDataTable, null).Tables[0];
                        if (rowcount_dt != null && rowcount_dt.Rows.Count > 0)
                        {
                            Int32.TryParse(rowcount_dt.Rows[0][0] + "", out rowscount);
                        }

                        copyDataTable = string.Empty;
                        if (rowscount > 1000000) //1000000 百万
                        {
                            if (MessageBox.Show(this,
                                "郑重提示,数据超过100万,将只保留前100万条数据,是否要继续操作?","郑重提示",
                                MessageBoxButtons.OKCancel).Equals(DialogResult.OK))
                            {
                                int colIndex = 0;
                                string _columns = string.Empty;

                                foreach (DataRow dr in dt.Rows)
                                {
                                    if (this.RowsContains(dr["名称"] + "", "ColumnName", column_dt))
                                    {
                                        if (colIndex > 0)
                                            _columns += "," + SnippetBase.GetColumnName(dr["名称"] + "", server.ProviderName);
                                        else
                                            _columns += " " + SnippetBase.GetColumnName(dr["名称"] + "", server.ProviderName);
                                        colIndex++;
                                    }
                                }

                                if (IdentityList.Count > 0)
                                    copyDataTable += "SET IDENTITY_INSERT " + SnippetBase.GetTableName(this.textBox1.Text, server.ProviderName) + " ON ";

                                copyDataTable += Environment.NewLine;
                                copyDataTable += "INSERT INTO " + SnippetBase.GetTableName(this.textBox1.Text, server.ProviderName) + " (" + _columns + ") ";
                                copyDataTable += Environment.NewLine;

                                switch (server.ProviderName)
                                {
                                    case "System.Data.SQL":
                                    case "System.Data.Sql":
                                        copyDataTable += "SELECT TOP 1000000 " + _columns + "";
                                        copyDataTable += " FROM " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text + "", server.ProviderName) + " ";
                                        break;
                                    case "System.Data.Sqlite":
                                        copyDataTable += "SELECT " + _columns + "";
                                        copyDataTable += " FROM " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text + "", server.ProviderName) + " LIMIT 1000000; ";
                                        break;
                                    case "System.Data.PostgreSql":
                                        copyDataTable += "SELECT " + _columns + "";
                                        copyDataTable += " FROM " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text + "", server.ProviderName) + " LIMIT 1000000; ";
                                        break;
                                }

                                copyDataTable += Environment.NewLine;

                                if (IdentityList.Count > 0)
                                    copyDataTable += "SET IDENTITY_INSERT " + SnippetBase.GetTableName(this.textBox1.Text, server.ProviderName) + " OFF";

                                db.Execute(CommandType.Text, copyDataTable, null);
                            }
                        }
                        else
                        {                         
                            int colIndex = 0;
                            string _columns = string.Empty;
                           
                            foreach (DataRow dr in dt.Rows)
                            {
                                if (this.RowsContains(dr["名称"] + "","ColumnName",column_dt))
                                {
                                    if (colIndex > 0)
                                        _columns += "," + SnippetBase.GetColumnName(dr["名称"] + "", server.ProviderName);
                                    else
                                        _columns += " " + SnippetBase.GetColumnName(dr["名称"] + "", server.ProviderName);
                                    colIndex++;
                                }
                            }

                            if (IdentityList.Count > 0)
                                copyDataTable += "SET IDENTITY_INSERT " + SnippetBase.GetTableName(this.textBox1.Text, server.ProviderName) + " ON ";

                            copyDataTable += Environment.NewLine;
                            copyDataTable += "INSERT INTO " + SnippetBase.GetTableName(this.textBox1.Text,server.ProviderName) + " (" + _columns + ") ";
                            copyDataTable += Environment.NewLine;
                            copyDataTable += "SELECT " + _columns + "";
                            copyDataTable += " FROM " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text + "", server.ProviderName)+" ";
                            copyDataTable += Environment.NewLine;

                            if (IdentityList.Count > 0)
                                copyDataTable += "SET IDENTITY_INSERT " + SnippetBase.GetTableName(this.textBox1.Text, server.ProviderName) + " OFF";

                            db.Execute(CommandType.Text, copyDataTable, null);

                        }

                        string dropTable = "Drop Table " + SnippetBase.GetTableName("_tem_" + this.textBox1.Text, server.ProviderName) + "";
                        db.Execute(CommandType.Text, dropTable, null);

                    }

                    //最后处理主键 
                    string PrimaryKeyTable = string.Empty;
                    foreach (var col in PrimaryKeyList)
                        PrimaryKeyTable += addPrimaryKeySql(server.ProviderName, this.textBox1.Text, col.Name);

                    if (!string.IsNullOrEmpty(PrimaryKeyTable))
                        db.Execute(CommandType.Text, PrimaryKeyTable, null);

                    db.Commit();

                    MessageBox.Show("保存成功");
                }
                catch (Exception ex)
                {
                    db.RollBack();
                    MessageBox.Show(ex.Message);
                }                

            }
            
        }

        private void ShowDDLCode()
        {
            StringPlus sp = new StringPlus();

            try
            {
                string createTable = "CREATE TABLE";
                //if (AppSettings.EditorSettings.KeywordsCase)
                //{
                //    createTable = "CREATE TABLE";
                //}
                //else
                //{
                //    createTable = "create table";
                //}

                if (AppSettings.EditorSettings.AutoSupplementary)
                {
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":
                            sp.Append("SET ANSI_NULLS ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append("SET QUOTED_IDENTIFIER ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append(Environment.NewLine);
                            sp.Append("SET ANSI_PADDING ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append(Environment.NewLine);
                            sp.Append("" + createTable + " [dbo].[" + table + "](");
                            break;
                        case "System.Data.Sqlite":
                            sp.Append("" + createTable + " [" + table + "](");
                            break;
                        case "System.Data.MySql":
                            sp.Append("" + createTable + " [" + table + "](");
                            break;
                        case "System.Data.PostgreSql":
                            sp.Append("" + createTable + " \"public\".\"" + table + "\"(");
                            break;
                    }
                }
                else
                {
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":
                            sp.Append("SET ANSI_NULLS ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append("SET QUOTED_IDENTIFIER ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append(Environment.NewLine);
                            sp.Append("SET ANSI_PADDING ON");
                            sp.Append(Environment.NewLine);
                            sp.Append("GO");
                            sp.Append(Environment.NewLine);
                            sp.Append(Environment.NewLine);
                            sp.Append("" + createTable + " dbo." + table + "(");
                            break;
                        case "System.Data.Sqlite":
                            sp.Append("" + createTable + " " + table + "(");
                            break;
                        case "System.Data.MySql":
                            sp.Append("" + createTable + " " + table + "(");
                            break;
                        case "System.Data.PostgreSql":
                            sp.Append("" + createTable + " public." + table + "(");
                            break;
                    }
                }

                sp.Append(Environment.NewLine);

                int index = 0;
                var dt = (this.dgv_design.DataSource as DataView).Table;

                List<Column> PrimaryKeyList = new List<Column>();
                List<Column> IdentityList = new List<Column>();

                foreach (DataRow row in dt.Rows)
                {
                    string identity = row["自增标识"] + "";
                    string isnull = row["允许为空"] + "";
                    string parmaryKey = row["主键标识"] + "";
                    string sqldataType = SnippetBase.getDataBaseDataType(row["数据类型"] + "", row["长度"] + "", server.ProviderName);
                    string defaultValue = row["默认值"] + "";

                    Column col = new Column();
                    col.Name = row["名称"] + "";

                    if (!string.IsNullOrEmpty(isnull) && Boolean.Parse(isnull))
                    {
                        isnull = "NULL";
                        col.IsNull = "NULL";
                    }
                    else
                    {
                        isnull = "NOT NULL";
                        col.IsNull = "NOT NULL";
                    }

                    if (identity == "自增")
                    {
                        identity = "IDENTITY(1,1)";
                        col.Auto_Identify = true;
                        IdentityList.Add(col);
                    }

                    col.Len = row["长度"] + "";
                    col.DataType = row["数据类型"] + "";                    

                    if (index > 0)
                    {
                        sp.Append("  ," + SnippetBase.GetColumnName(row["名称"] + "", server.ProviderName) + " " + sqldataType + " " + identity + " " + isnull + "  ");
                    }
                    else
                    {
                        sp.Append("   " + SnippetBase.GetColumnName(row["名称"] + "", server.ProviderName) + " " + sqldataType + " " + identity + " " + isnull + "  ");
                    }

                    if (parmaryKey == "Pk")
                    {
                        col.Pk_Identify = true;
                        PrimaryKeyList.Add(col);
                    }

                    if (!string.IsNullOrEmpty(defaultValue))
                    {
                        switch (server.ProviderName)
                        {
                            case "System.Data.MySql":
                            case "System.Data.PostgreSql":
                                sp.Append(" DEFAULT " + defaultValue + " ");
                                break;
                        }                        
                    }

                    string description = row["描述"] + "";
                    if (!string.IsNullOrEmpty(description))
                    {
                        sp.Append(" --" + description + "");
                    }                    

                    sp.Append(Environment.NewLine);
                    index++;
                }

                //生成主键脚本

                if (PrimaryKeyList != null && PrimaryKeyList.Count > 0)
                {
                    //sp.Append(System.Environment.NewLine);
                    sp.Append("  ,CONSTRAINT PK_" + table + " PRIMARY KEY ");
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":
                            sp.Append("CLUSTERED");
                            break;
                    }
                    sp.Append(System.Environment.NewLine);
                    sp.Append("  (");
                    sp.Append(System.Environment.NewLine);
                    int primaryKeyIndex = 0;
                    foreach (var PrimaryKey in PrimaryKeyList)
                    {
                        sp.Append("    ");
                        if (primaryKeyIndex > 0)
                        {
                            sp.Append(",");
                        }
                        sp.Append(SnippetBase.GetColumnName(PrimaryKey.Name, server.ProviderName) + " ASC ");
                        sp.Append(System.Environment.NewLine);
                        primaryKeyIndex++;
                    }
                    //sp.Append(System.Environment.NewLine);
                    sp.Append("  ) ");
                    switch (server.ProviderName)
                    {
                        case "System.Data.Sql":
                        case "System.Data.SQL":
                            sp.Append("WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
                            break;
                        case "System.Data.PostgreSql":

                            break;
                    }
                    sp.Append(System.Environment.NewLine);
                }

                sp.Append(")");
                sp.Append(Environment.NewLine);

                switch (server.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":

                        sp.Append("GO");
                        sp.Append(Environment.NewLine);

                        sp.Append(Environment.NewLine);
                        sp.Append("SET ANSI_PADDING OFF");
                        sp.Append(Environment.NewLine);
                        sp.Append("GO");
                        sp.Append(Environment.NewLine);

                        break;

                    case "System.Data.Sqlite":

                        sp.Append(Environment.NewLine);

                        break;

                    case "System.Data.MySql":

                        sp.Append(Environment.NewLine);

                        break;

                    case "System.Data.PostgreSql":

                        sp.Append(Environment.NewLine);
                        sp.Append("WITH ( OIDS=FALSE ); ");
                        sp.Append(Environment.NewLine);
                        sp.Append("ALTER TABLE public."+table+" ");
                        sp.Append(Environment.NewLine);
                        sp.Append("OWNER TO "+server.UserID+" ; ");
                        sp.Append(Environment.NewLine);

                        break;

                }

                //添加字段注释
                foreach (DataRow row in dt.Rows)
                {
                    string columnName = row["名称"] + "";
                    string description = row["描述"] + "";
                    if (!string.IsNullOrEmpty(description))
                    {
                        switch (server.ProviderName)
                        {
                            case "System.Data.Sql":
                            case "System.Data.SQL":

                                sp.Append(Environment.NewLine);
                                sp.Append("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'" + description + "' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'" + table + "', @level2type=N'COLUMN',@level2name=N'" + columnName + "'");
                                sp.Append(Environment.NewLine);
                                sp.Append("GO");

                                break;

                            case "System.Data.PostgreSql":

                                if (AppSettings.EditorSettings.AutoSupplementary)
                                {
                                    sp.Append(Environment.NewLine);
                                    sp.Append("COMMENT ON COLUMN \"" + table + "\".\"" + columnName + "\" IS '" + description + "';");
                                    //sp.Append(Environment.NewLine);
                                    //sp.Append("GO");
                                }
                                else
                                {
                                    sp.Append(Environment.NewLine);
                                    sp.Append("COMMENT ON COLUMN " + table + "." + columnName + " IS '" + description + "';");
                                    //sp.Append(Environment.NewLine);
                                    //sp.Append("GO");
                                }

                                break;

                        }
                    }
                }

                //字段加默认值                
                foreach (DataRow row in dt.Rows)
                {
                    string columnName = row["名称"] + "";
                    string defaultValue= row["默认值"] + "";
                    if (!string.IsNullOrEmpty(defaultValue))
                    {
                        switch (server.ProviderName)
                        {
                            case "System.Data.Sql":
                            case "System.Data.SQL":

                                sp.Append(System.Environment.NewLine);
                                sp.Append(System.Environment.NewLine);
                                sp.Append("ALTER TABLE [dbo].[" + table + "] ADD  CONSTRAINT [DF_" + table + "_" + columnName + "]  DEFAULT ((" + defaultValue + ")) FOR [" + columnName + "]");
                                sp.Append(System.Environment.NewLine);
                                sp.Append("GO");

                                break;
                        }
                    }
                }

                //获取表索引
                if (indexTabRequired)
                {
                    DataTable indexesTable = (DataTable)dgv_indexes.DataSource;
                    foreach (DataRow indexesTableRow in indexesTable.Rows)
                    {
                        if (indexesTableRow["index_type"] + "" == "聚集")
                            continue;

                        sp.Append(System.Environment.NewLine);
                        sp.Append(System.Environment.NewLine);
                        sp.Append("--添加索引" + indexesTableRow["index_name"] + "");
                        sp.Append(System.Environment.NewLine);
                        string script = indexesTableRow["index_statement"] + "";
                        sp.Append(script);
                        sp.Append(System.Environment.NewLine);
                        sp.Append("GO");
                    }
                }


                //获取表触发器
                if (triggerTabRequired)
                {
                    DataTable triggerTable = (DataTable)dgv_trigger.DataSource; //(dgv_trigger.DataSource as DataView).Table;
                    foreach (DataRow triggerTableRow in triggerTable.Rows)
                    {
                        sp.Append(System.Environment.NewLine);
                        sp.Append(System.Environment.NewLine);
                        string script = triggerTableRow["trigger_statement"] + "";
                        sp.Append(script);
                    }
                }

            }
            catch(Exception ex)
            {
                sp.Append(ex.Message);
                DevelopAssistant.Common.NLogger.WriteToLine(ex.Message, "错误",
                    DateTime.Now, ex.Source, ex.StackTrace);
            }

            this.textEditorControl1.Text = sp.Value;
            this.textEditorControl1.Invalidate();

        }

        private bool RowsContains(string value, string column,DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                if ((row[column] + "").Equals(value))
                    return true;
            }
            return false;
        }

        private string addPrimaryKeySql(string ProviderName, string tableName, string columnName)
        {
            string val = string.Empty;
            switch (ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    val = "alter table [" + tableName + "] add constraint PK_" + tableName + " primary key([" + columnName + "])";
                    break;
                case "System.Data.Sqlite":
                    val = "alter table [" + tableName + "] add constraint PK_" + tableName + " primary key([" + columnName + "])";
                    break;
                case "System.Data.PostgreSql":
                    val = "alter table \"" + tableName + "\" add constraint \"PK_" + tableName + "\" primary key(\"" + columnName + "\")";
                    break;

            }
            return val;
        }

        private void dgv_design_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex > -1 && e.ColumnIndex == -1) 
                {                    
                    Rectangle rect= dgv_design.GetCellDisplayRectangle(1, e.RowIndex, true);
                    Point MousePt = new Point(e.X, e.Y);
                    MousePt.Offset(rect.X - dgv_design.RowHeadersWidth, rect.Y);
                    dgv_design.ClearSelection();                  
                    dgv_design.Rows[e.RowIndex].Selected = true;
                    contextMenuStrip1.Show(dgv_design, MousePt);                   
                }
            }
        }

        private void dgv_design_MouseClick(object sender, MouseEventArgs e)
        {
                     
        }

        private void 添加列ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            int selectedRowIndex = 0;
            if (dgv_design.SelectedRows.Count > 0)
                selectedRowIndex = dgv_design.SelectedRows[0].Index;
            else
                selectedRowIndex = dgv_design.Rows.Count;
          
            DataTable dt = (dgv_design.DataSource as DataView).Table;
            DataRow row = dt.NewRow();            
            row[5] = true;
            dt.Rows.InsertAt(row, selectedRowIndex);
        }

        private void 删除列ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dgv_design.SelectedRows.Count > 0)
            {
                foreach (DataGridViewRow row in dgv_design.SelectedRows)
                {
                    dgv_design.Rows.Remove(row);
                }                
            }
        }

        private void 上移ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dgv_design.SelectedRows.Count > 0)
            {
                DataTable dt = ((DataView)dgv_design.DataSource).Table;
                int index = dgv_design.SelectedRows[0].Index;

                if (index > -1)
                {
                    int preIndex = index - 1;
                    int nextIndex = index + 1;

                    if (preIndex < 0)
                        preIndex = 0;

                    if (nextIndex > dt.Rows.Count - 1)
                        nextIndex = dt.Rows.Count - 1;

                    if (index != preIndex)
                    {
                        DataTable newdt = dt.Clone();
                        newdt.Rows.Clear();

                        DataRow dr1 = dt.Rows[index];
                        DataRow dr2 = dt.Rows[preIndex];

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (i == index)
                            {
                                newdt.ImportRow(dr2);
                            }
                            else if (i == preIndex)
                            {
                                newdt.ImportRow(dr1);
                            }
                            else
                            {
                                newdt.ImportRow(dt.Rows[i]);
                            }
                        }

                        dgv_design.DataSource = newdt.DefaultView;

                        SetDataGridViewSeletedRow(preIndex);

                        dgv_design.Invalidate();

                    }
                }
            }

        }

        private void 下移ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            if (dgv_design.SelectedRows.Count > 0)
            {
                DataTable dt = ((DataView)dgv_design.DataSource).Table;
                int index = dgv_design.SelectedRows[0].Index;

                if (index > -1)
                {
                    int preIndex = index - 1;
                    int nextIndex = index + 1;

                    if (preIndex < 0)
                        preIndex = 0;

                    if (nextIndex > dt.Rows.Count - 1)
                        nextIndex = dt.Rows.Count - 1;

                    if (index != nextIndex)
                    {
                        DataTable newdt = dt.Clone();
                        newdt.Rows.Clear();

                        DataRow dr1 = dt.Rows[index];
                        DataRow dr2 = dt.Rows[nextIndex];

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            if (i == index)
                            {
                                newdt.ImportRow(dr2);
                            }
                            else if (i == nextIndex)
                            {
                                newdt.ImportRow(dr1);
                            }
                            else
                            {
                                newdt.ImportRow(dt.Rows[i]);
                            }
                        }

                        dgv_design.DataSource = newdt.DefaultView;

                        SetDataGridViewSeletedRow(nextIndex);

                        dgv_design.Invalidate();

                    }
                }
            }
        }

        private void 设为主键ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dgv_design.SelectedRows.Count > 0)
            {
                dgv_design.EndEdit();

                var cisnull = dgv_design.SelectedRows[0].Cells[5].Value;
                if (cisnull != null && cisnull != DBNull.Value && Boolean.Parse(cisnull + ""))
                {
                    MessageBox.Show("设为主键的列不能允许为空");
                    return;
                }                    

                foreach (DataGridViewRow row in dgv_design.Rows)
                {
                    var value = row.Cells[4].Value;
                    if (value != null && value != DBNull.Value)
                    {
                        //if (value.ToString().Contains("Pk") && row.Index == dgv_design.SelectedRows[0].Index)
                        //    return;

                        if (value.ToString().Contains("Pk") && row.Index == dgv_design.SelectedRows[0].Index)
                        {
                            row.Cells[4].Value = value.ToString().Replace("Pk", "");
                            continue;
                        }

                        if (value.ToString().Contains("Pk"))
                        {
                            row.Cells[4].Value = value.ToString().Replace("Pk", "");
                        }
                        else if (row.Index == dgv_design.SelectedRows[0].Index)
                        {
                            row.Cells[4].Value += "Pk" + value;
                        }
                        
                    }
                }

                ((dgv_design.DataSource) as DataView).Table.AcceptChanges();

                dgv_design.Invalidate();
            }            
        }

        private void SetDataGridViewSeletedRow(int index)
        {
            dgv_design.ClearSelection();
            dgv_design.Rows[index].Selected = true;
        }

        private void btnShowCode_Click(object sender, EventArgs e)
        {
            ShowDDLCode();
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            TabPage tabPage = tabControl1.SelectedTab;
            switch (tabPage.Name)
            {
                case "tableTabPage":
                    break;
                case "triggerTabPage":
                    break;
                case "indexesTabPage":                    
                    break;
                case "codeTabPage":
                    ShowDDLCode();
                    break;
            }
        }

        private void btnCopyto_Click(object sender, EventArgs e)
        {
            string ScriptText = this.textEditorControl1.Text;
            Clipboard.Clear();
            Clipboard.SetDataObject(ScriptText);
            MessageBox.Show(this, "已复制到剪贴板", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        public override void OnThemeChanged(EventArgs e)
        {
            Color foreColor = SystemColors.WindowText;
            Color backColor = SystemColors.Control;
            Color toolBackColor = SystemColors.Control;
            Color textBackColor = SystemColors.Window;
            Color toolBorderColor = SystemColors.ControlLight;
            Color toolStripBackColor= SystemColors.ControlLight;
            string themeName = AppSettings.EditorSettings.TSQLEditorTheme;
            switch (themeName)
            {
                case "Default":
                    foreColor = SystemColors.WindowText;
                    backColor = SystemColors.Control;
                    textBackColor = SystemColors.Window;
                    toolBackColor = SystemColors.Control;
                    toolBorderColor = SystemColors.ControlLight;
                    tabControl1.ForeColor = Color.Black;
                    tabControl1.BorderColor = SystemColors.ControlDark;
                    tabControl1.ArrowColor = Color.White;
                    tabControl1.BaseColor = SystemColors.Control;
                    tabControl1.BackColor = SystemColors.Control;
                    tabControl1.SelectedColor = SystemColors.Control;
                    tabControl1.BaseTabColor = SystemColors.Control;
                    toolStripBackColor = Color.FromArgb(246, 248, 250);                  
                    break;
                case "Black":
                    foreColor = Color.FromArgb(240, 240, 240);
                    backColor = Color.FromArgb(045, 045, 048);
                    textBackColor = Color.FromArgb(038, 038, 038);
                    toolBackColor = Color.FromArgb(038, 038, 038);
                    toolBorderColor = SystemColors.ControlDark;
                    tabControl1.ForeColor = Color.FromArgb(240, 240, 240);
                    tabControl1.BorderColor = SystemColors.ControlDark;
                    tabControl1.ArrowColor = Color.White;
                    tabControl1.BaseColor = Color.FromArgb(045, 045, 048);
                    tabControl1.BackColor = Color.FromArgb(045, 045, 048);
                    tabControl1.SelectedColor = Color.FromArgb(045, 045, 048);
                    tabControl1.BaseTabColor = Color.FromArgb(045, 045, 048);
                    toolStripBackColor = Color.FromArgb(062, 062, 062);                    
                    break;
            }            

            panel1.ForeColor = foreColor;
            panel1.BackColor = toolBackColor;
            panel2.ForeColor = foreColor;
            panel2.BackColor = backColor;
            panel3.ForeColor = foreColor;
            panel3.BackColor = backColor;

            this.tableTabPage.BackColor = backColor;
            this.indexesTabPage.BackColor = backColor;
            this.triggerTabPage.BackColor = backColor; 
            this.codeTabPage.BackColor = backColor;

            this.dgv_design.SetTheme(themeName);
            this.dgv_indexes.SetTheme(themeName);
            this.dgv_trigger.SetTheme(themeName);
            this.toolStrip1.SetTheme(themeName);
            this.toolStrip1.BackColor = toolStripBackColor;
            this.ToolBox.ForeColor = foreColor;
            this.ToolBox.BackColor = toolStripBackColor;
            this.ToolBox.BorderStyle = ICSharpCode.WinFormsUI.Controls.NBorderStyle.None;
            this.ToolBox.BorderColor = toolBorderColor;
            this.textBox1.XForeColor = foreColor;
            this.textBox1.XBackColor = textBackColor; 

            this.btnCopyto.BackColor = toolStripBackColor;           

            btnApplyOk.ForeColor = foreColor;
            btnApplyOk.BackColor = toolBackColor;
            btnCancle.ForeColor = foreColor;
            btnCancle.BackColor = toolBackColor;

            this.textEditorControl1.SetHighlighting(themeName, "TSQL");
        }
    }   

}
